﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Web;

namespace T04WebService
{
    public class NewsDao
    {
        //MySQL的连接串描述
        private static string connStr = "server =localhost;user=root;passwords=;database=gametest;sslmode =none;";

        /// <summary>
        /// 获取指定分类的记录
        /// </summary>
        /// <param name="num">获取数量</param>
        /// <param name="category">分类</param>
        /// <returns></returns>
        public static List<News> LoadData(int num, int category) {
            //构建用戶集合
            List<News> newsList = new List<News>();

            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();
                //模糊查询
                string sql = "SELECT * FROM tb_news WHERE category = @category ORDER BY publish_time DESC LIMIT 0,@num ";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                //安全的参数（占位符）
                cmd.Parameters.AddWithValue("@category", category);
                cmd.Parameters.AddWithValue("@num", num);
                MySqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    News news = new News();
                    news.Id = Convert.ToInt64(rdr["id"]);
                    news.Id = Convert.ToInt64(rdr["id"]);
                    news.Title = Convert.ToString(rdr["title"]);
                    news.Content = Convert.ToString(rdr["content"]);
                    news.PublishTime = Convert.ToDateTime(rdr["publis_time"]);
                    news.Url = Convert.ToString(rdr["url"]);
                    news.Category = Convert.ToInt32(rdr["category"]);
                    newsList.Add(news);
                }
                rdr.Close();
            }
            catch (Exception e)
            {
                Debug.WriteLine(e.ToString());
            }
            finally
            {
                if (conn != null)
                { 
                    conn.Close();
               }
        }
            return newsList;
        }
    }
}